﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace BuildSql
{
    public class SqlTable
    {
        private List<SqlColumn> _columns = new List<SqlColumn>();

        public SqlTable(string tableName, string xPathToRoot, params SqlColumn[] columns)
        {
            Name = tableName;
            XPathToRootElement = xPathToRoot;
            _columns.AddRange(columns);
        }

        public string Name { get; private set; }

        public string XPathToRootElement { get; private set; }

        public IEnumerable<SqlColumn> Columns { get { return _columns; } }

        public StringBuilder GenerateSQL(XmlDocument doc)
        {
            StringBuilder builder = new StringBuilder();

            //Build the table definition
            builder.AppendFormat("CREATE TABLE '{0}' (", Name);
            builder.AppendLine();
            foreach (var columnDef in Columns)
            {
                builder.AppendFormat("\t'{0}' {1},", columnDef.Name, columnDef.SqlTypeDefinition);
                builder.AppendLine();
            }
            builder.AppendLine("\t);");
            builder.AppendLine();

            //Build the SQL to insert the values
            foreach (var rootNode in doc.SelectNodes(XPathToRootElement).OfType<XmlNode>())
            {
                List<string> values = new List<string>();
                foreach (var column in Columns)
                {
                    string value;
                    if (!Program.TryGetConstantValue(column.XPathToValue, out value))
                    {
                        XmlNode valueNode = rootNode.SelectSingleNode(column.XPathToValue);
                        if (valueNode.Value == null)
                        {
                            value = valueNode.InnerText;
                        }
                        else
                        {
                            value = valueNode.Value;
                        }
                    }
                    values.Add(value);
                }
                builder.AppendFormat("INSERT INTO '{0}' VALUES('{1}');", Name, string.Join("', '", values.ToArray()));
                builder.AppendLine();
            }

            return builder;
        }
    }
}
